package com.kinglone.common.poi; /**
 * 
 */

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;


/**
 *
 * Description: Excel操作
 * 
 * CreateTime: 2017年12月11日  下午3:08:09
 *
 * Change History:
 *
 *        Date             CR Number              Name              Description of change
 *
 */
public class ExcelUtil {
 
	private static final String EXCEL_XLS = "xls";  
    private static final String EXCEL_XLSX = "xlsx";  
  
    /** 
     * 判断Excel的版本,获取Workbook 
     * @param in 
     * @param file
     * @return 
     * @throws IOException 
     */  
    public static Workbook getWorkbok(InputStream in,File file) throws IOException{  
        Workbook wb = null;  
        if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003  
            wb = new HSSFWorkbook(in);  
        }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
            wb = new XSSFWorkbook(in);
        }  
        return wb;  
    }  
  
    /** 
     * 判断文件是否是excel 
     * @throws Exception  
     */  
    public static void checkExcelVaild(File file) throws Exception{  
        if(!file.exists()){  
            throw new Exception("文件不存在");  
        }  
        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){  
            throw new Exception("文件不是Excel");  
        }  
    }  
    
    /** 
     * 读取Excel测试，兼容 Excel 2003/2007/2010 
     * @throws Exception  
     */  
    public static void main(String[] args) throws Exception {  
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");  
        try {
            Map<String,String> item=new HashMap();
            item.put("useuntname","左林公园");
            item.put("eqpCod","4202T2121");
            item.put("isp_date","2018-12-02");
            // 同时支持Excel 2003、2007  
            File excelFile = new File("D:/file/template/modExcel/3B001B/0004/record.xlsx"); // 创建文件对象
            FileInputStream in = new FileInputStream(excelFile); // 文件流  
            checkExcelVaild(excelFile);  
            Workbook workbook = getWorkbok(in,excelFile);  
            //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的  

            int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
            //开始测试内容替换
            for(int i=0;i<sheetCount;i++){
                Sheet sheet = workbook.getSheetAt(i);
                for(int j=0;j<=sheet.getLastRowNum();j++){
                    Row row =  sheet.getRow(j);
                    if(row==null) continue;//为空就跳过
                    for(int n=0;n<=row.getLastCellNum();n++){
                        Cell cell=  row.getCell(n);

                        if(cell==null) {
                            continue;
                        }
                        //此处判断cell类型并获取成string
                        String value =getCellValue(cell);
                        value= cell.getStringCellValue();
                        if(StringUtils.isNotEmpty(value)) {
                            String newValue=replaceMap(item,value);
                            cell.setCellValue(newValue);
                        }

                    }
                }

            }
            // 输出文件
            FileOutputStream fileOut = new FileOutputStream("D:/file/template/modExcel/3B001B/0004/result.xlsx");
            workbook.write(fileOut);
            fileOut.close();

        } catch (Exception e) {  
            e.printStackTrace();  
        }
    }

    /**
     * 判断cell格式并获取值
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getStringCellValue();
    }

    /**
     * 对比标签,相同返回标签值,不同返回null
     * 标签自动加上 ${}
     * @param item
     * @param context 单元格里面的内容
     * @return
     */
    private static String replaceMap(Map<String, String> item, String context) {

        Set<Map.Entry<String, String>> entrys = item.entrySet();
        for (Map.Entry<String, String> entry : entrys) {
            String key = entry.getKey();//获取key值
            if(StringUtils.isEmpty(entry.getValue())){
                entry.setValue("");
            }
            key = "${" + key + "}";//加上通用封装
            //判断context里面是否包含key
            if(context.indexOf(key)>=0){
                //截取替换
                int firstIndex=context.indexOf(key);
                int length=key.length();
                context = context.substring(0,firstIndex)+entry.getValue()+context.substring(firstIndex+length);

            }else{
                //不包含就返不处理
            }
        }
        return context;
    }

    private static Object getValue(Cell cell) {
    	Object obj = null;
    	switch (cell.getCellTypeEnum()) {
	        case BOOLEAN:
	            obj = cell.getBooleanCellValue(); 
	            break;
	        case ERROR:
	            obj = cell.getErrorCellValue(); 
	            break;
	        case NUMERIC:
	            obj = cell.getNumericCellValue(); 
	            break;
	        case STRING:
	            obj = cell.getStringCellValue(); 
	            break;
	        default:
	            break;
    	}
    	return obj;
    }
}
